"""
数据操作
"""
import re
import json
import math

import lark_oapi as lark
from common.client import get_client
from spreadsheet.ranks import clear as clear_sheet,delete
from spreadsheet.sheets import get as get_sheet,add as add_sheet


def over_data(spreadsheet_token,sheet_name, data,rg=None):
    # 覆盖写入,需要知道现在的行列大小 删除至一行 然后重新覆盖写入
    if not data:
        return
    # 预处理 清空表格或添加表格
    sheet = get_sheet(spreadsheet_token, sheet_name)
    if sheet:
        clear_sheet(spreadsheet_token, sheet_name)
    else:
        add_sheet(spreadsheet_token, sheet_name)

    __do_over_data(spreadsheet_token,sheet_name, data,rg)


def over_data_by_column_name(spreadsheet_token,sheet_name,column_name, data,start_row=1):
    title = ready_title(spreadsheet_token, sheet_name)
    index = title.index(column_name)
    over_data_by_column_index(spreadsheet_token,sheet_name,index,data,start_row)


def over_data_by_column_index(spreadsheet_token,sheet_name,column_index, data,start_row=1):
    column = __get_column_name(column_index)
    # 列表转二维列表
    data = [[data[i]] for i in range(len(data))]
    # 暂不处理清空当前列功能
    __do_over_data(spreadsheet_token,sheet_name, data,f"{column}{start_row}:{column}")

def append_data(spreadsheet_token, sheet_name, data):
    """
    追加数据到指定的表格中，支持分批写入（每次最多 5000 行）。
    """
    try:
        # 数据校验
        if not isinstance(data, list) or not data:
            raise ValueError("数据必须是非空的列表")

        # 获取 sheet 和 client
        sheet = get_sheet(spreadsheet_token, sheet_name)
        if not sheet:
            add_sheet(spreadsheet_token, sheet_name)
        # 如果 data > 5000 行，则分批写入
        if len(data) > 5000:
            count = len(data)
            batches = math.ceil(count / 5000)
            for i in range(batches):
                start = i * 5000
                end = min((i + 1) * 5000, count)
                print(f"正在写入第 {i + 1}/{batches} 批数据，范围: {start} - {end}")
                __append_data( spreadsheet_token, sheet, data[start:end])
        else:
            print(f"正在写入数据，范围: 0 - {len(data)}")
            __append_data(spreadsheet_token, sheet, data)
    except Exception as e:
        raise

def ready_title(spreadsheet_token, sheet_name,row=1):
    """ 读取标题 """
    # 优化读取数据，小于1则为1
    row = 1 if row < 1 else row

    client = get_client()
    sheet = get_sheet(spreadsheet_token, sheet_name)
    column_count = sheet.grid_properties.column_count
    end_column = __get_column_name(column_count - 1)
    uri = f"/open-apis/sheets/v2/spreadsheets/{spreadsheet_token}/values/{sheet.sheet_id}!A{row}:{end_column}{row}"
    request: lark.BaseRequest = lark.BaseRequest.builder() \
            .http_method(lark.HttpMethod.GET) \
            .uri(uri) \
            .token_types({lark.AccessTokenType.TENANT}) \
            .build()

    response: lark.BaseResponse = client.request(request)
    if not response.success():
        raise RuntimeError(f"读取数据失败,失败原因:{response.msg}")
    result = json.loads(str(response.raw.content, lark.UTF_8))
    return result['data']['valueRange']['values'][0]

def __get_column_name(index):
    column_name = ""
    while index >= 0:
        remainder = index % 26
        column_name = chr(65 + remainder) + column_name
        index = index // 26 - 1
        if index < 0:
            break
    return column_name

def __get_end_column_name(data):
    max_length = max(len(sublist) for sublist in data)
    return __get_column_name(max_length - 1)


def __do_over_data(spreadsheet_token,sheet_name, data,rg=None):
    sheet = get_sheet(spreadsheet_token, sheet_name)
    try:
        if rg:
            rg = __parse_excel_range(rg)

        # 如果 data > 5000 行，则分批写入
        start_column = rg.get('start_column') if rg else "A"
        start_row = rg.get('start_row') if rg else 1
        end_column = rg.get('end_column') if rg else __get_end_column_name(data)
        if len(data) > 5000:
                count = len(data)
                batches = math.ceil(count / 5000)
                for i in range(batches):
                    start = i * 5000
                    end = min((i + 1) * 5000, count)
                    print(f"正在写入第 {i + 1}/{batches} 批数据，范围: {start} - {end}")
                    __over_data(spreadsheet_token, sheet, data[start:end],f"{start_column}{start + start_row}:{end_column}{end + start_row-1}")
        else:
                print(f"正在写入数据，范围: 0 - {len(data)}")
                __over_data(spreadsheet_token, sheet, data,f"{start_column}{start_row}:{end_column}{len(data) + start_row -1}")
    except Exception as e:
        raise

def ready(spreadsheet_token, sheet_name,rg=None):
    client = get_client()
    sheet = get_sheet(spreadsheet_token, sheet_name)
    rg = f"{sheet.sheet_id}!{rg}" if rg else sheet.sheet_id
    uri = f"/open-apis/sheets/v2/spreadsheets/{spreadsheet_token}/values/{rg}"
    request: lark.BaseRequest = lark.BaseRequest.builder() \
            .http_method(lark.HttpMethod.GET) \
            .uri(uri) \
            .token_types({lark.AccessTokenType.TENANT}) \
            .build()

    response: lark.BaseResponse = client.request(request)
    if not response.success():
        raise RuntimeError(f"读取数据失败,失败原因:{response.msg}")
    result = json.loads(str(response.raw.content, lark.UTF_8))
    return result['data']['valueRange']['values']

def __over_data(spreadsheet_token,sheet,data,rg=None):
    client = get_client()
    uri = f"/open-apis/sheets/v2/spreadsheets/{spreadsheet_token}/values"
    rg = f"{sheet.sheet_id}!{rg}" if rg else sheet.sheet_id
    param = {
        "valueRange": {
            "range": rg,
            "values": data
        }
    }

    request: lark.BaseRequest = lark.BaseRequest.builder() \
        .http_method(lark.HttpMethod.PUT) \
        .uri(uri) \
        .token_types({lark.AccessTokenType.TENANT}) \
        .body(param) \
        .build()

    response: lark.BaseResponse = client.request(request)
    if not response.success():
        raise RuntimeError(f"写入数据失败,失败原因:{response.msg}")

def __append_data(spreadsheet_token,sheet,data,rg=None):
    client = get_client()
    uri = f"/open-apis/sheets/v2/spreadsheets/{spreadsheet_token}/values_append"
    rg = f"{sheet.sheet_id}!{rg}" if rg else sheet.sheet_id
    param = {
        "valueRange":{
            "range": rg,
            "values": data
        }
    }

    request: lark.BaseRequest = lark.BaseRequest.builder() \
        .http_method(lark.HttpMethod.POST) \
        .uri(uri) \
        .token_types({lark.AccessTokenType.TENANT}) \
        .body(param) \
        .build()

    response: lark.BaseResponse = client.request(request)
    if not response.success():
        raise RuntimeError(f"追加数据失败,失败原因:{response.msg}")

def __parse_excel_range(cell_range):
    """
    解析 Excel 单元格范围字符串，提取起始列名、起始行数和结束列名。

    参数:
        cell_range (str): 要解析的单元格范围字符串，例如 "A1:CV100" 或 "A:CV"

    返回:
        dict: 包含 start_column, start_row, end_column 的字典
              如果输入无效，则返回 None
    """
    # 定义正则表达式模式
    pattern = r'^([A-Za-z]+)(\d*):([A-Za-z]+)(\d*)$'

    match = re.fullmatch(pattern, cell_range)

    if not match:
        # 尝试匹配没有行号的情况，如 "A:CV"
        pattern_no_row = r'^([A-Za-z]+):([A-Za-z]+)$'
        match = re.fullmatch(pattern_no_row, cell_range)
        if not match:
            return None  # 无效格式

    # 提取匹配组
    start_col = match.group(1)
    start_row_str = match.group(2) if len(match.groups()) > 2 and match.group(2) else None
    end_col = match.group(3) if len(match.groups()) > 3 else match.group(2)  # 处理没有行号的情况

    # 如果起始或结束行号存在，则转换为整数
    start_row = int(start_row_str) if start_row_str else None

    # 验证列名是否有效（仅包含字母）
    if not re.fullmatch(r'^[A-Za-z]+$', start_col):
        return None
    if not re.fullmatch(r'^[A-Za-z]+$', end_col):
        return None

    return {
        'start_column': start_col,
        'start_row': start_row,
        'end_column': end_col
    }